package com.zty.dao;

import com.zty.bean.Field;
import com.zty.bean.Index;
import com.zty.bean.Table;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class Dao {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  public List<Table> getTableBySchema(String schema) {
    return jdbcTemplate.query("select t.TABLE_NAME, t.TABLE_COMMENT, GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index) AS PK from information_schema.TABLES t left join information_schema.STATISTICS s on s.TABLE_NAME=t.TABLE_NAME where t.TABLE_SCHEMA='" + schema + "' and t.TABLE_COMMENT <> '' and s.INDEX_NAME='PRIMARY' GROUP BY s.TABLE_SCHEMA,s.TABLE_NAME,s.index_name;", (resultSet, i) -> {
      Table table = new Table();
      table.setName(resultSet.getString("TABLE_NAME"));
      table.setComment(resultSet.getString("TABLE_COMMENT"));
      table.setPk(resultSet.getString("PK"));
      return table;
    });
  }

  public List<Field> getFieldByTable(String table) {
    return jdbcTemplate.query("select COLUMN_NAME ,COLUMN_TYPE ,COLUMN_COMMENT, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where table_name = '" + table + "';", (resultSet, i) -> {
      Field field = new Field();
      field.setName(resultSet.getString("COLUMN_NAME"));
      field.setType(resultSet.getString("COLUMN_TYPE"));
      field.setComment(resultSet.getString("COLUMN_COMMENT"));
      field.setNotNull(resultSet.getString("IS_NULLABLE").equalsIgnoreCase("yes"));
      return field;
    });
  }

  public List<Index> getIndexByTable(String table) {
    return jdbcTemplate.query("SELECT a.NON_UNIQUE, a.INDEX_NAME, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `content` FROM information_schema.statistics a where a.INDEX_NAME <> 'PRIMARY' And a.TABLE_NAME = '" + table + "' GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name;", (resultSet, i) -> {
      Index field = new Index();
      field.setName(resultSet.getString("INDEX_NAME"));
      field.setContent(resultSet.getString("content"));
      field.setUnique(resultSet.getInt("NON_UNIQUE") == 0);
      return field;
    });
  }
}
